#!/usr/bin/perl
# 
# Read the departments data
# Insert data to sqlite database
# Author Hui Liu
use strict;
use warnings;
use DBI;

my $mapFile = $ARGV[0]? $ARGV[0]:"shortName.txt";
my $dbh = DBI->connect("dbi:SQLite:../assets/tourMap.db","","",{RaiseError => 1, sqlite_unicode => 1, AutoCommit => 1});

open my $fh, $mapFile or die "Can't open file $mapFile";

my $bname;
my $lat;
my $lng;

# clear table

#$dbh->do("DROP TABLE IF EXISTS departments;");
#$dbh->do("CREATE TABLE departments (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , name TEXT, details TEXT, locationID INTEGER, link TEXT, FOREIGN KEY(locationID) REFERENCES locations(_id));");
            $dbh->do("update locations SET shortName = null;") or die "Couldn't excute statement: " . $dbh->errstr;

my $locIndex;

while ( my $line = <$fh>) {
    chomp $line;
    
    while ($line =~ /<img.+?chco=FFcc66,FF9900.+?<area title=\"(.+?)\s+?\((\w+?)\)\".+?<\/map>/g) {
        # find building id
        my $route = $dbh->prepare('SELECT _id FROM locations WHERE name = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;
        $route->execute($1) or die "Couldn't execute statement: " . $route->errstr;
        print $1;
        if (my @row = $route->fetchrow_array()){
            $locIndex = $row[0];
        
        
       
            print "update locations SET shortName = \"$2\" WHERE _id = $locIndex;";
            $dbh->do("update locations SET shortName = \"$2\" WHERE _id = $locIndex") or die "Couldn't excute statement: " . $dbh->errstr;
        }
    }
}



exit;
